-- @author lixiyong01
-- @date 2023.07.03
-- 任务运行超过6h优化告警

use hdp_lbg_supin_zplisting;

add jar viewfs://58-cluster/home/hdp_lbg_supin/resultdata/offline_warehouse3/udf/warehouse_tool-1.0.18.1-shaded.jar;
create temporary function SendOndutyUDF AS 'com.bj58.adsp.dp.hive.offline_udf.SendOndutyUDF';

-- SendOndutyUDF： eventId,subject,description,responser,severity,rgId,ctiId,agentId,agentSecret,notifyRg,notifyAdmin
insert overwrite table app_zp_rock_task_timeout6h_2onduty partition (dt=${dateSuffix})
select
	event_id, subject, detail, responser,
	SendOndutyUDF(
	    event_id, subject, detail, responser, 3,
	    'rg-20220408165440319qtek0b',
	    '2023070316250700389jaik',
	    'app_zp_rock_20230703001',
	    '000000', 0, 1
	) as send_result
from (
    select
        concat(from_unixtime(unix_timestamp(),'yyyyMMddHHmmss'),cast(rand() * 100000000 as int)) as event_id,
        '任务执行超6h告警' as subject,
        concat(
            '任务名称：',task_name,
            ', 用时（分）：', round(used_minute,2),
            ', 起止时间：',start_time, ', ',finish_time,
            ', 任务链接：', 'https://dp.58corp.com/data-develop/task-list/task-detail/',t1.task_id
        ) as detail,
        user_oa as responser,
        user_team_name as team
    from (
        select task_id, task_name, user_oa, user_team_name, start_time, finish_time,
            (unix_timestamp(finish_time) - unix_timestamp(start_time))/60 as used_minute
        from hdp_lbg_supin_zplisting.app_zp_rock_task_inst
        where dt='${#date(0, 0, -1):yyyyMMdd#}'
        and unix_timestamp(finish_time) - unix_timestamp(start_time) > 6*60*60
        and type=1 and task_state=400

        union all
        select username, table_name,
            collect_list(
                concat('表名称：',database_name,'.',table_name,', 认领状态：',is_claim, ', 任务名称：', task_name, ', 任务链接：', 'https://dp.58corp.com/data-develop/task-list/task-detail/',task_id)
            ) as detail_list
        from (
            select table_id, nvl(database_name,'-') as database_name, nvl(table_name,'-') as table_name, owner_id,
                nvl(is_claim,'-') as is_claim, task_id, nvl(task_name,'-') as task_name,  owner_id2, if(is_claim==1,owner_id,owner_id2) as username
            from hdp_lbg_supin_zplisting.app_zp_rock_task_no_depend
            where dt='${#date(0, 0, -1):yyyyMMdd#}' and task_id is not null
        ) n
        where username!='null'
        group by username,table_name

    ) t1
    left join (
        select task_id, task_owner
        from (
            select `item_key` as task_owner,split( `item_value`,',') as task_ids
            from hdp_lbg_supin_zplisting.dim_zp_tbl_data_dapan_dim_dict_define_all
            where dt=${#date(0,0,-1):yyyyMMdd#} and action in(0,1) and type_code='任务运行超6h白名单'
        ) t0
        lateral view explode(task_ids) items as task_id
    ) t2 on t1.task_id = t2.task_id
    where t2.task_id is null and user_team_name like '技术支撑线%'
    limit 100
) t



